package Laborum2.repository;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import Laborum2.Basicas.*;

@Component
public class JdbcEstudiosRepository implements EstudiosRepository{
	
	List <DetalleEstudios> est = new ArrayList <DetalleEstudios>();
	
	DetalleEstudios estudios =new DetalleEstudios();
	
	@Autowired
	private DataSource dataSource;

	
	public List<DetalleEstudios> obtenerEstudios(int dni) {		
		 String sql = "SELECT * from DetalleEstudios where dni=?" ;
		
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			try {
				conn = dataSource.getConnection();
				ps = conn.prepareStatement(sql);
				ps.setInt(1, dni);
			
				rs = ps.executeQuery();
				
				while (rs.next()) {	
				estudios =new DetalleEstudios();
				estudios.setId(rs.getInt("id"));
				estudios.setDni(rs.getInt("dni"));
				estudios.setEstudios(rs.getInt("estudios"));
				estudios.setFecini(rs.getDate("fecini"));	
				estudios.setFecfin(rs.getDate("fecfin"));
				estudios.setTipo(rs.getString("tipo"));
				est.add(estudios);    

			   }
				
	           
			} catch (SQLException ex) {

			}
			closeConecction(rs, ps, conn);
	
	return est;

	}

	public List<DetalleEstudios> obtenerEstudiosDNI(int id,int dni) {
		 String sql = "SELECT * from DetalleEstudios where dni=? and estado =1 and id=?" ;
			
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			try {
				conn = dataSource.getConnection();
				ps = conn.prepareStatement(sql);
				ps.setInt(1, dni);
				ps.setInt(2, id);
				rs = ps.executeQuery();

				while (rs.next()) {
				estudios =new DetalleEstudios();
				estudios.setDni(rs.getInt("dni"));	
				estudios.setEstudios(rs.getInt("estudios"));
				estudios.setFecini(rs.getDate("fecini"));	
				estudios.setFecfin(rs.getDate("fecfin"));
				estudios.setTipo(rs.getString("tipo"));
		         
		        est.add(estudios);
	              
			}
	           
			} catch (SQLException ex) {

			}
			closeConecction(rs, ps, conn);
	
	return est;
		
	}
	
    public void ingresar(DetalleEstudios estudios) {
	      
    	
		String sql = "insert into DetalleEstudios (dni,estudios, fecini, fecfin,tipo,estado) values (?,?,?,?,?,?)";

                Connection conn = null;
                PreparedStatement ps = null;

             try {
                  conn = dataSource.getConnection();
                  ps = conn.prepareStatement(sql);            
                   ps.setInt(1, estudios.getDni());
                   ps.setInt(2, estudios.getEstudios());
                   ps.setDate(3, (Date) estudios.getFecini());
                   ps.setDate(4, (Date) estudios.getFecfin());
                   ps.setString(5,estudios.getTipo());
                   ps.setInt(6, estudios.getEstado());
                 

               ps.execute();

              

           } catch (SQLException ex) {
             }
            closeConecction1(ps, conn);
	
	   
	   
   }
	
	public void eliminar(int id,int dni) {
		
		String sql = "update DetalleEstudios set estado=0 where id=? and dni=?";

		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = dataSource.getConnection();
			ps = conn.prepareStatement(sql);
		
			ps.setInt(1, id);
			ps.setInt(2, dni);
			
			ps.execute();
		} catch (SQLException ex) {
		}
		closeConecction1(ps, conn);	
		
		
	}

	public void editar(DetalleEstudios estudios){
			
		 if (obtenerEstudiosDNI(estudios.getId(),estudios.getDni()).size() > 0) {
                //estudios,dni, fecini, fecfin,tipo,estado
				String sql = "update DetalleEstudios set dni=?,estudios=?,fecini=?,fecfin=?,tipo=?,estado=?" +
						"where dni=? and id=?";

				Connection conn = null;
				PreparedStatement ps = null;

				try {
					conn = dataSource.getConnection();
					ps = conn.prepareStatement(sql);
					   ps.setInt(1, estudios.getDni());
	                   ps.setInt(2, estudios.getEstudios());
	                   ps.setDate(3, (Date) estudios.getFecini());
	                   ps.setDate(4, (Date) estudios.getFecfin());
	                   ps.setString(5,estudios.getTipo());
	                   ps.setInt(6, estudios.getEstado());

							ps.execute();
				} catch (SQLException ex) {
				}
				closeConecction1(ps, conn);


		   }
			
	 }		
			
	private void closeConecction(ResultSet rs, PreparedStatement ps,Connection conn) {

			if (rs != null) {
				try {
					// Close to prevent database cursor exhaustion
					rs.close();
				} catch (SQLException ex) {
				}
			}
			if (ps != null) {
				try {
					// Close to prevent database cursor exhaustion
					ps.close();
				} catch (SQLException ex) {
				}
			}
			if (conn != null) {
				try {
					// Close to prevent database connection exhaustion
					conn.close();
				} catch (SQLException ex) {
				}
			}

		}

	private void closeConecction1(PreparedStatement ps, Connection conn) {

			if (ps != null) {
				try {
					// Close to prevent database cursor exhaustion
					ps.close();
				} catch (SQLException ex) {
				}
			}
			if (conn != null) {
				try {
					// Close to prevent database connection exhaustion
					conn.close();
				} catch (SQLException ex) {
				}
			}

		}

		
}